package com.anzhen.interview.poi;

import com.google.common.base.CharMatcher;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ReadExecl {
    public static void main(String[] args) throws Exception {
        CharMatcher.digit().retainFrom("");

    }


    public static void writeXlsx(boolean split) {
        String fileName="test_11.xlsx";
        Map<Integer, List<String[]>> map = new HashMap<>();
        ArrayList<String[]> value = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            String[] strings = new String[100];
            for (int j = 0; j < 100; j++) {
                strings[j] = j + "";
            }
            value.add(strings);
        }
        map.put(0, value);

        try {
            XSSFWorkbook wb = new XSSFWorkbook();
            for (int sheetnum = 0; sheetnum < map.size(); sheetnum++) {
                XSSFSheet sheet = wb.createSheet("" + sheetnum);
                List<String[]> list = map.get(sheetnum);
                for (int i = 0; i < list.size(); i++) {
                    if (split) {
                        CellRangeAddress cra=new CellRangeAddress(0, 0, 0, 9);
                        //在sheet里增加合并单元格
                        sheet.addMergedRegion(cra);
                    }
                    XSSFRow row = sheet.createRow(i);
                    String[] str = list.get(i);
                    for (int j = 0; j < str.length; j++) {
                        XSSFCell cell = row.createCell(j);
                        if (split && j < 9 && i == 0) {
                            if (j == 0) {
                                cell.setCellValue("我是被合并的");
                            }
                        } else {
                            cell.setCellValue(str[j]);
                        }
                    }
                }
            }
            FileOutputStream outputStream = new FileOutputStream(fileName);
            wb.write(outputStream);
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取Excel测试，兼容 Excel 2003/2007/2010
     */
    public static String readExcel() {
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        try {
            //同时支持Excel 2003、2007
            File excelFile = new File("/Users/zhenan/Downloads/test.xlsx"); //创建文件对象
            FileInputStream is = new FileInputStream(excelFile); //文件流
            Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的
            int sheetCount = workbook.getNumberOfSheets();  //Sheet的数量
            //遍历每个Sheet
            for (int s = 0; s < sheetCount; s++) {
                Sheet sheet = workbook.getSheetAt(s);
                int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数
                //遍历每一行
                for (int r = 0; r < rowCount; r++) {
                    Row row = sheet.getRow(r);
                    int cellCount = row.getPhysicalNumberOfCells(); //获取总列数
                    //遍历每一列
                    for (int c = 0; c < cellCount; c++) {
                        Cell cell = row.getCell(c);
                        int cellType = cell.getCellType();
                        String cellValue = null;
                        switch (cellType) {
                            case Cell.CELL_TYPE_STRING: //文本
                                cellValue = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_NUMERIC: //数字、日期
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    cellValue = fmt.format(cell.getDateCellValue()); //日期型
                                } else {
                                    cellValue = String.valueOf(cell.getNumericCellValue()); //数字
                                }
                                break;
                            case Cell.CELL_TYPE_BOOLEAN: //布尔型
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case Cell.CELL_TYPE_BLANK: //空白
                                cellValue = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_ERROR: //错误
                                cellValue = "错误";
                                break;
                            case Cell.CELL_TYPE_FORMULA: //公式
                                cellValue = "错误";
                                break;
                            default:
                                cellValue = "错误";
                        }
                        System.out.print(cellValue + "    ");
                    }
                    System.out.println();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


}
